package com.basker.pisces.orm.parser;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import com.basker.pisces.core.sequence.ILongSequrenceGenerator;
import com.basker.pisces.orm.data.meta.DataSetMeta;
import com.basker.pisces.orm.data.meta.FieldMeta;
import com.basker.pisces.orm.data.meta.TableMeta;
import com.basker.pisces.orm.session.WhereCondition;
import com.basker.pisces.orm.statement.BatchWriteStatement;
import com.basker.pisces.orm.statement.FieldObject;
import com.basker.pisces.orm.statement.InsertCommand;
import com.basker.pisces.orm.statement.PKFieldObject;
import com.basker.pisces.orm.statement.SelectStatement;
import com.basker.pisces.orm.statement.UpdateCommand;
import com.basker.pisces.utils.GUIDUtils;

public abstract class AbstractSqlParser implements ISqlParser {

    @Autowired
    private ILongSequrenceGenerator generator;

    @Override
    public BatchWriteStatement getDeleteStatement(TableMeta tableMeta, List<Object> deletePkValues) {
        if (CollectionUtils.isEmpty(deletePkValues)) {
            return null;
        }

        String sql = this.genDeleteSql(tableMeta);
        List<Object[]> batchArguments = deletePkValues.stream().map(pk -> new Object[]{pk})
                .collect(Collectors.toList());
        int[] argumentTypes = new int[]{tableMeta.getPkFieldMeta().getDataField().getJDBCTypeNumber()};

        return new BatchWriteStatement(sql, batchArguments, argumentTypes);
    }

    @Override
    public List<BatchWriteStatement> getDeleteStatements(DataSetMeta dataSetMeta, List<Object> deletePkValues) {
        TableMeta rootTableMeta = dataSetMeta.getRootTableMeta();

        String tableName = rootTableMeta.getName();
        String pkFieldName = rootTableMeta.getPkFieldMeta().getName();
        String delCondition = MessageFormat.format("{0}.{1} = ?", tableName, pkFieldName);

        List<BatchWriteStatement> statements = new ArrayList<BatchWriteStatement>();
        this.genDeleteStatements(statements, rootTableMeta, rootTableMeta, delCondition);

        for (BatchWriteStatement statement : statements) {
            for (Object deletePkValue : deletePkValues) {
                statement.addArguments(new Object[]{deletePkValue});
            }
        }

        return statements;

    }

    @Override
    public List<BatchWriteStatement> getInsertStatements(TableMeta tableMeta, List<InsertCommand> insertCommands) {
        if (CollectionUtils.isEmpty(insertCommands)) {
            return Collections.emptyList();
        }

        this.setPKValues(tableMeta, insertCommands);

        Map<String, BatchWriteStatement> statements = new LinkedHashMap<>();

        for (InsertCommand insertCommand : insertCommands) {
            Collection<FieldObject> fields = insertCommand.getFields();

            String key = insertCommand.getKey();
            BatchWriteStatement statement = statements.get(key);

            if (statement == null) {
                String sql = this.genInsertSql(tableMeta, insertCommand);
                int[] argTypes = this.getArgTypes(fields);

                statement = new BatchWriteStatement(sql, argTypes);

                statements.put(key, statement);
            }

            Object[] args = this.getArgValues(fields);
            statement.addArguments(args);
        }

        return new ArrayList<>(statements.values());
    }

    @Override
    public SelectStatement getSelectStatement(DataSetMeta dataSetMeta, TableMeta tableMeta, WhereCondition condition) {
        String sql = this.genSelectSQL(dataSetMeta.getRootTableMeta(), tableMeta, condition);

        return new SelectStatement(sql, condition.getArgs(), condition.getArgTypes());
    }

    @Override
    public List<BatchWriteStatement> getUpdateStatements(TableMeta tableMeta, List<UpdateCommand> updateCommands) {
        if (CollectionUtils.isEmpty(updateCommands)) {
            return Collections.emptyList();
        }

        Map<String, BatchWriteStatement> statements = new LinkedHashMap<>();

        for (UpdateCommand updateCommand : updateCommands) {
            Collection<FieldObject> fields = updateCommand.getFields();

            String key = updateCommand.getKey();
            BatchWriteStatement statement = statements.get(key);

            if (statement == null) {
                String sql = this.genUpdateSql(tableMeta, updateCommand);
                int[] argTypes = this.getArgTypes(fields);

                statement = new BatchWriteStatement(sql, argTypes);

                statements.put(key, statement);
            }

            Object[] args = this.getArgValues(fields);
            statement.addArguments(args);
        }

        return new ArrayList<>(statements.values());
    }

    protected String genDeleteSql(TableMeta rootTableMeta, TableMeta delTable, String delCondition) {
        StringBuilder sql = new StringBuilder();

        sql.append("DELETE ").append(delTable.getName());
        genFrom(rootTableMeta, delTable, delCondition, sql);

        return sql.toString();
    }

    protected void genFrom(TableMeta rootTableMeta, TableMeta tableMeta, String strWhere, StringBuilder sql) {
        String tableName = tableMeta.getName();

        StringBuilder sbFrom = new StringBuilder(MessageFormat.format(" FROM {0}", tableName));
        // 当前表
        TableMeta preTableMeta = tableMeta;
        // 当前表的父级表
        TableMeta parentTableMeta = tableMeta.getParentTableMeta();

        Set<String> joinedTables = new HashSet<String>(8);

        while (parentTableMeta != null) {
            String parentTableName = parentTableMeta.getName();// 父表表名
            String parentTablePkFieldName = parentTableMeta.getPkFieldMeta().getName();// 父表主键
            String preTableName = preTableMeta.getName();// 当前表表名
            String preTableFkFieldName = preTableMeta.getFkFieldMeta().getName();// 当前表外键

            String joinOn = MessageFormat.format(" INNER JOIN {0} ON {0}.{1} = {2}.{3}", parentTableName,
                    parentTablePkFieldName, preTableName, preTableFkFieldName);
            sbFrom.append(joinOn);

            joinedTables.add(parentTableMeta.getName());

            // 重新设置当前表等于当前表的父表（就是把父表当成当前表）
            preTableMeta = parentTableMeta;
            // 重新设置父表等于当前父表的父表（当前父表上面一句已经把它设置成当前表了）
            parentTableMeta = parentTableMeta.getParentTableMeta();

        }
        // 获取所传的表的主键
        if (tableMeta.getPkFieldMeta() != null) {
            // String tablePkFieldName =
            String tablePkFieldName = tableMeta.getPkFieldMeta().getName();
            // 获取所传的表的扩展表集合
            List<TableMeta> subtables = tableMeta.getSubTableMetas();
            if (subtables.size() > 0) {
                for (TableMeta subtable : subtables) {
                    String subTableName = subtable.getName();

                    if (joinedTables.contains(subTableName)) {
                        continue;
                    }

                    String joinOn = MessageFormat.format(" LEFT JOIN {0} ON {0}.{2} = {1}.{2}", subTableName, tableName,
                            tablePkFieldName);

                    sbFrom.append(joinOn);

                    joinedTables.add(subTableName);
                }
            } else if (rootTableMeta.getSubTableMetas().contains(tableMeta)) {
                String rootTableName = rootTableMeta.getName();
                String rootTablePkFieldName = rootTableMeta.getPkFieldMeta().getName();

                if (!joinedTables.contains(rootTableName)) {

                    String joinOn = MessageFormat.format(" INNER JOIN {0} ON {0}.{1} = {2}.{3}", rootTableName,
                            rootTablePkFieldName, tableName, tablePkFieldName);

                    sbFrom.append(joinOn);
                }
            }
        }

        if (sbFrom.length() > 0) {
            sql.append(sbFrom.toString());
        }

        if (!StringUtils.isEmpty(strWhere)) {
            sql.append(" WHERE ").append(strWhere);
        }
    }

    protected String genInsertSql(TableMeta tableMeta, InsertCommand insertCommand) {
        String tableName = tableMeta.getName();
        Collection<FieldObject> insertFields = insertCommand.getInsertFields();

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(tableName);

        StringBuilder fields = new StringBuilder();
        StringBuilder values = new StringBuilder();

        for (FieldObject field : insertFields) {
            fields.append(field.getName()).append(",");
            values.append("?,");
        }

        fields.deleteCharAt(fields.length() - 1);
        values.deleteCharAt(values.length() - 1);

        sql.append("(").append(fields).append(")");
        sql.append("VALUES(").append(values).append(")");

        return sql.toString();
    }

    protected String genSelectSQL(TableMeta rootTableMeta, TableMeta tableMeta, WhereCondition condition) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        int i = 0;
        for (FieldMeta field : tableMeta.getSelectFieldMetas()) {
            if (genSelectField(tableMeta, sql, i, field)) {
                i++;
            }
        }
        if (i > 0) {
            sql.deleteCharAt(sql.length() - 1);
        }
        this.genFrom(rootTableMeta, tableMeta, condition.getWhereSql(), sql);
        return sql.toString();
    }

    protected String genUpdateSql(TableMeta tableMeta, UpdateCommand updateCommand) {
        String tableName = tableMeta.getName();
        Collection<FieldObject> updateFields = updateCommand.getUpdateFields();

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE ").append(tableName);
        sql.append(" SET ");

        for (FieldObject field : updateFields) {
            sql.append(field.getName()).append(" = ?").append(",");
        }

        sql.deleteCharAt(sql.length() - 1);

        FieldObject pkFieldObject = updateCommand.getPkFieldObject();
        String pkFieldName = pkFieldObject.getName();

        sql.append(" WHERE ").append(pkFieldName).append(" = ?");

        FieldObject timestampFieldObject = updateCommand.getTimestampFieldObject();
        if (timestampFieldObject != null) {
            sql.append(" AND ").append(timestampFieldObject.getName()).append(" = ?");
        }

        return sql.toString();
    }

    protected void setPKValues(TableMeta tableMeta, Collection<InsertCommand> insertCommands) {
        List<PKFieldObject> pkLongFieldObjs = null;
        List<PKFieldObject> pkGUIDFieldObjs = null;

        for (InsertCommand command : insertCommands) {
            PKFieldObject pkFieldObject = (PKFieldObject) command.getPkFieldObject();
            if (pkFieldObject.getValue() == null) {
                Class<?> fieldClass = pkFieldObject.getDataField().getFieldClass();
                if (fieldClass.equals(String.class)) {
                    if (pkGUIDFieldObjs == null) {
                        pkGUIDFieldObjs = new ArrayList<PKFieldObject>(insertCommands.size());
                    }

                    pkGUIDFieldObjs.add(pkFieldObject);
                } else {
                    if (pkLongFieldObjs == null) {
                        pkLongFieldObjs = new ArrayList<PKFieldObject>(insertCommands.size());
                    }

                    pkLongFieldObjs.add(pkFieldObject);
                }
            }
        }

        if (!CollectionUtils.isEmpty(pkLongFieldObjs)) {
            String tableName = tableMeta.getName();
            Long[] pkValues = this.getLongPKValue(tableName, pkLongFieldObjs.size());

            int i = 0;
            for (PKFieldObject pkFieldObject : pkLongFieldObjs) {
                pkFieldObject.setPkValue(pkValues[i]);
                i++;
            }
        }

        if (!CollectionUtils.isEmpty(pkGUIDFieldObjs)) {
            String[] pkValues = this.getStringPKValue(pkGUIDFieldObjs.size());

            int i = 0;
            for (PKFieldObject pkFieldObject : pkGUIDFieldObjs) {
                pkFieldObject.setPkValue(pkValues[i]);
                i++;
            }
        }
    }

    private String genDeleteSql(TableMeta tableMeta) {
        String tableName = tableMeta.getName();
        String pkFieldName = tableMeta.getPkFieldMeta().getName();

        return MessageFormat.format("DELETE FROM {0} WHERE {1} = ?", tableName, pkFieldName);
    }

    private void genDeleteStatements(List<BatchWriteStatement> statements, TableMeta rootTableMeta,
                                     TableMeta delTableMeta, String delCondition) {

        for (TableMeta childTableMeta : delTableMeta.getChildTableMetas()) {
            genDeleteStatements(statements, rootTableMeta, childTableMeta, delCondition);
        }

        for (TableMeta subTable : delTableMeta.getSubTableMetas()) {
            genDeleteStatements(statements, rootTableMeta, subTable, delCondition);
        }

        String sql = this.genDeleteSql(rootTableMeta, delTableMeta, delCondition);
        BatchWriteStatement statement = new BatchWriteStatement(sql,
                new int[]{rootTableMeta.getPkFieldMeta().getDataField().getJDBCTypeNumber()});

        statements.add(statement);
    }

    private boolean genSelectField(TableMeta tableMeta, StringBuilder sb, int i, FieldMeta field) {
        String tableName = field.getTableName() == null ? tableMeta.getName() : field.getTableName();
        // tableName = handleDbObjectName(tableName);

        String fieldName = field.getName();
        if (!StringUtils.isEmpty(fieldName)) {
            // fieldName = handleDbObjectName(fieldName);
            sb.append(MessageFormat.format("{0}.{1},", tableName, fieldName));

            field.setSelectIndex(i);
            return true;
        }

        return false;
    }

    private int[] getArgTypes(Collection<FieldObject> fields) {
        int[] argTypes = new int[fields.size()];

        int i = 0;
        for (FieldObject field : fields) {
            argTypes[i++] = field.getDataField().getJDBCTypeNumber();
        }

        return argTypes;
    }

    private Object[] getArgValues(Collection<FieldObject> fields) {
        return fields.stream().map(f -> f.getValue()).toArray();
    }

    private Long[] getLongPKValue(String tableName, int size) {
        Long[] pkValues = new Long[size];

        for (int i = 0; i < pkValues.length; i++) {
            pkValues[i] = generator.generate(tableName);
        }

        return pkValues;
    }

    private String[] getStringPKValue(int size) {
        String[] pkValues = new String[size];

        for (int i = 0; i < pkValues.length; i++) {
            pkValues[i] = GUIDUtils.generateGUID();
        }

        return pkValues;
    }

}
